package com.fpno.preparedStatement.crud;

import com.fpno.bean.Order;
import com.fpno.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;

/*
* 针对order表的查询操作
* */
public class OrderForQuery {
    /*
    * 针对表的字段名与类的属性名不相同的情况：
    * 1 必须在声明sql时，使用类的属性名来命名字段的别名
    * 2 使用ResultSetMetaData时，需要使用getColumnLabel()来替代，获取类的别名
    * 若sql语句中没有别名。getColumnLabel则是的是列名
    * */
    @Test
    public void testOrderForQuery() throws Exception {
        String sql="select order_id orderId,order_name orderName  from test.order where order_id=?";
        Order order = orderForQuery(sql, 4);
        System.out.println(order);
    }




    /*
    * 针对order表的查询操作
    * */
    public Order orderForQuery(String sql,Object...args) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i=0;i<args.length;i++){
            ps.setObject(i+1,args[i]);
        }
        //执行获取结果集
        ResultSet resultSet = ps.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        if (resultSet.next()){
            Order order = new Order();
            for (int i = 0; i < columnCount; i++) {
                Object columnValue = resultSet.getObject(i + 1);
                //获取列的列名
                //String columnName = metaData.getColumnName(i + 1);
                //获取列的别名
                String columnName = metaData.getColumnLabel(i + 1);
                Field field = Order.class.getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(order,columnValue);

            }
            JDBCUtils.closeResource(connection,ps,resultSet);
            return order;
        }
        JDBCUtils.closeResource(connection,ps,resultSet);
        return null;
    }








    @Test
    public void testQuery1() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql="select * from test.order where order_id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1,1);
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()){
            int id = (int) resultSet.getObject(1);
            String name = resultSet.getString(2);
            Date date = resultSet.getDate(3);
            Order order = new Order(id,name,date);
            System.out.println(order);
        }
        JDBCUtils.closeResource(connection,ps,resultSet);
    }
}
